#!/usr/bin/env node

const Database = require('better-sqlite3');
const path = require('path');

const DB_PATH = path.join(process.cwd(), 'data', 'delivery-management.db');

console.log('🧹 开始快速数据库清理...\n');

try {
  const db = new Database(DB_PATH);
  
  console.log('1. 分析数据库表...');
  
  // 获取所有表
  const allTables = db.prepare("SELECT name FROM sqlite_master WHERE type='table' ORDER BY name").all();
  console.log(`   总表数: ${allTables.length}`);
  
  // 找出空的设备实例表
  const emptyInstanceTables = [];
  const instanceTables = allTables.filter(t => t.name.startsWith('device_instances_'));
  
  console.log('2. 检查设备实例表...');
  instanceTables.forEach(table => {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM ${table.name}`).get();
      if (count.count === 0) {
        emptyInstanceTables.push(table.name);
      }
    } catch (e) {
      console.log(`   警告: 无法查询表 ${table.name}`);
    }
  });
  
  console.log(`   设备实例表总数: ${instanceTables.length}`);
  console.log(`   空表数量: ${emptyInstanceTables.length}`);
  
  // 找出空的参数表
  const emptyParamTables = [];
  const paramTables = allTables.filter(t => t.name.startsWith('table_c') && !t.name.includes('_params_'));
  
  console.log('3. 检查参数表...');
  paramTables.forEach(table => {
    try {
      const count = db.prepare(`SELECT COUNT(*) as count FROM ${table.name}`).get();
      if (count.count === 0) {
        emptyParamTables.push(table.name);
      }
    } catch (e) {
      console.log(`   警告: 无法查询表 ${table.name}`);
    }
  });
  
  console.log(`   参数表总数: ${paramTables.length}`);
  console.log(`   空参数表数量: ${emptyParamTables.length}`);
  
  // 计算要删除的表
  const tablesToDelete = [...emptyInstanceTables, ...emptyParamTables];
  
  if (tablesToDelete.length === 0) {
    console.log('✅ 没有发现需要清理的空表');
    db.close();
    return;
  }
  
  console.log(`\n4. 准备删除 ${tablesToDelete.length} 个空表:`);
  console.log(`   - 空设备实例表: ${emptyInstanceTables.length}个`);
  console.log(`   - 空参数表: ${emptyParamTables.length}个`);
  
  // 开始事务删除
  console.log('\n5. 执行删除操作...');
  const deleteTransaction = db.transaction(() => {
    let deletedCount = 0;
    
    tablesToDelete.forEach(tableName => {
      try {
        db.prepare(`DROP TABLE IF EXISTS [${tableName}]`).run();
        deletedCount++;
        if (deletedCount % 50 === 0) {
          console.log(`   已删除 ${deletedCount}/${tablesToDelete.length} 个表`);
        }
      } catch (e) {
        console.log(`   错误: 无法删除表 ${tableName}: ${e.message}`);
      }
    });
    
    return deletedCount;
  });
  
  const deletedCount = deleteTransaction();
  
  console.log('\n6. 优化数据库...');
  db.pragma('optimize');
  db.prepare('VACUUM').run();
  
  // 检查结果
  const finalTables = db.prepare("SELECT name FROM sqlite_master WHERE type='table'").all();
  
  console.log('\n✅ 清理完成!');
  console.log(`   删除了 ${deletedCount} 个空表`);
  console.log(`   数据库表数量: ${allTables.length} → ${finalTables.length}`);
  console.log(`   性能提升: ${((allTables.length - finalTables.length) / allTables.length * 100).toFixed(1)}%`);
  
  db.close();
  
} catch (error) {
  console.error('❌ 清理过程中发生错误:', error.message);
  process.exit(1);
}